package com.iris.live.services.schedule;

import com.iris.live.services.common.KPIUtils;
import com.iris.live.services.data.repositories.impl.BaseRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;

import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 经销商销售管理报表定时任务
 */
@Service("FIPerformanceTrackingRptSchecule")
public class FIPerformanceTrackingRptSchecule extends BaseRepository {
    private static Logger log = LoggerFactory.getLogger(FIPerformanceTrackingRptSchecule.class);

    //@Scheduled(cron = "0 50 00 * * ?")
    @Transactional
    public void saveFIPerformanceTrackingRptSchecule() {
        try {
            log.info("*************************F&I Performance Tracking Report定时任务START***************************");
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(new Date());
            calendar.add(Calendar.DAY_OF_MONTH, -1);
            Date date = calendar.getTime();
            String year = new SimpleDateFormat("yyyy").format(date);
            String month = new SimpleDateFormat("MM").format(date);
            this.saveFIPerformanceTrackingRptData(year, month);
            log.info("*************************F&I Performance Tracking Report定时任务END************************");
        } catch (Exception e) {
            log.error("F&I Performance Tracking Report定时任务执行失败", e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }

    /**
     * 数据生成
     *
     * @param year
     * @param month
     * @throws Exception
     */
    public void saveFIPerformanceTrackingRptData(String year, String month) throws Exception {

        /*//预先生成此年月的表数据
        initReport(year);

        //将月份month转换成表字段，如month=01转成JAN
        String monthEN = parseMonthToEn(month);

        //统计
        String selectSql = null;
        Map<String, Object> params = new HashMap<>();


        String updateSql = "update t_fi_performance_tracking_rpt set " + monthEN + "=:value  where PERMIT=:PERMIT and KPI_NAME=:KPI_NAME and SYEAR=:SYEAR ";

        //ROS_SMART,ROS_MB 当月累计开票-当月累计退票，只含零售
        selectSql = "SELECT a.许可, " +
                "sum(if(a.`开票时间` is not null and   b.品牌='smart', 1,0))- " +
                "sum(if(a.`退票时间` is not null  and   b.品牌='smart' and a.`退票时间` > a.开票时间,1,0)) as ROS_SMART, " +
                "sum(if(a.`开票时间` is not null and   (b.品牌='MM' or b.品牌='MB' or b.品牌='AMG'), 1,0))- " +
                "sum(if(a.`退票时间` is not null  and   (b.品牌='MM' or b.品牌='MB' or b.品牌='AMG') and a.`退票时间` > a.开票时间,1,0)) as ROS_MB " +
                "FROM vw_retailed_quotation a, LSH车型 b    " +
                "WHERE  date_format(a.`开票时间`,'%Y-%m')=:ym  " +
                "AND a.车型编号 = b.车型编号 " +
                "group by a.许可";
        params.put("ym", year + "-" + month);
        List<Map<String, Object>> values = this.getList(selectSql.toString(), params);
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "ROS_SMART");
            doUpdateFIReport(values, updateSql, year, "ROS_MB");
        }
        //MB新保台数,smart新保台数
        //MB+MM+AMG 当月累计新保出单数-当月累计批退--MB新保台数
        //smart 当月累计新保出单数-当月累计批退 smart新保台数
        selectSql = "SELECT a.许可,  " +
                "SUM(IF(e.品牌='smart',1,0)) as smart新保台数,  " +
                "SUM(IF((e.品牌='MM' or e.品牌='MB' or e.品牌='AMG'),1,0)) as MB新保台数,  " +
                "sum(IFNULL(c.交强险手续费,0)+IFNULL(c.商业险手续费,0))  as 新保总手续费 " +
                "FROM vw_retailed_quotation a      " +
                "LEFT JOIN `报价保险表` c ON c.`保险编号`=a.`保险编号`  " +
                "JOIN LSH车型 e ON a.`车型编号` = e.`车型编号`   " +
                "LEFT JOIN( " +
                "       SELECT * FROM lsh保险申请表附属_详细 " +
                "       WHERE  第N年=1 " +
                "       GROUP BY `初始报价单号` " +
                "       ) d ON d.初始报价单号 = a.初始报价单号 AND d.退保日期 IS  NULL " +
                "where  a.`是否驳回`='否'  " +
                "AND  DATE_FORMAT(c.交强险出单日期,'%Y-%m') =:ym  " +
                "GROUP BY  a.许可";
        values = this.getList(selectSql.toString(), params);
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "smart新保台数");
            doUpdateFIReport(values, updateSql, year, "MB新保台数");
            doUpdateFIReport(values, updateSql, year, "新保总手续费");
        }

        //smart首年保险费,MB首年保险费
        selectSql = "SELECT a.许可,  " +
                "SUM(IF(f.`险种`<>'车船税' and f.`折后价` is not null and e.品牌='smart',f.`折后价`,0)) AS smart首年保险费,  " +
                "SUM(IF(f.`险种`<>'车船税' and f.`折后价` is not null and (e.品牌='MM' or e.品牌='MB' or e.品牌='AMG'),f.`折后价`,0)) AS MB首年保险费  " +
                "FROM vw_retailed_quotation a     " +
                "LEFT JOIN `报价保险表` c ON c.`保险编号`=a.`保险编号`  " +
                "JOIN `报价保险详细表` f ON f.`保险编号`=a.`保险编号` AND f.`第N年`='1'  " +
                "JOIN LSH车型 e ON a.`车型编号` = e.`车型编号` " +
                "LEFT JOIN( " +
                "       SELECT * FROM lsh保险申请表附属_详细  " +
                "       WHERE  第N年=1  " +
                "       GROUP BY `初始报价单号` " +
                "       ) d ON d.初始报价单号 = a.初始报价单号 AND d.退保日期 IS  NULL " +
                "where  a.`是否驳回`='否'  " +
                "AND  DATE_FORMAT(c.交强险出单日期,'%Y-%m') =:ym       " +
                "GROUP BY  a.许可";
        values = this.getList(selectSql.toString(), params);
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "smart首年保险费");
            doUpdateFIReport(values, updateSql, year, "MB首年保险费");
        }

        //MB金融台数,smart金融台数,MB先享后选台数,smart保值租购台数,smart金融手续费,MB金融手续费,
        // smart金融额外服务费,MB金融额外服务费,smart贷款总金额,MB贷款总金额
                *//*"select a.许可,SUM(IF(e.品牌='smart' AND  f.`状态`='11',1,0))-sum(if(a.`退票时间` is not null and a.`退票时间` > a.开票时间 AND e.品牌='smart' AND  f.`状态`='11',1,0)) as smart金融台数,  " +
                "SUM(IF((e.品牌='MM' or e.品牌='MB' or e.品牌='AMG') AND  f.`状态`='11',1,0))-sum(if(a.`退票时间` is not null and a.`退票时间` > a.开票时间 AND (e.品牌='MM' or e.品牌='MB' or e.品牌='AMG') AND  f.`状态`='11',1,0)) as MB金融台数,  " +
                "SUM(IF(b.金融机构='奔驰租赁' and (e.品牌='MM' or e.品牌='MB' or e.品牌='AMG'),1,0))-sum(if(a.`退票时间` is not null and a.`退票时间` > a.开票时间 and (e.品牌='MM' or e.品牌='MB' or e.品牌='AMG') AND  f.`状态`='11',1,0))  as MB先享后选台数,  " +
                "SUM(IF(b.金融机构='奔驰租赁' and e.品牌='smart' AND  f.`状态`='11',1,0))-sum(if(a.`退票时间` is not null and a.`退票时间` > a.开票时间 and e.品牌='smart' AND  f.`状态`='11',1,0))  as smart保值租购台数,  " +
                "SUM(IF(e.品牌='smart' AND  f.`状态`='11',b.`金融手续费`,0))/1.17 as smart金融手续费,  " +
                "SUM(IF((e.品牌='MM' or e.品牌='MB' or e.品牌='AMG') AND  f.`状态`='11',b.`金融手续费`,0))/1.17 as MB金融手续费,  " +
                "SUM(IF(e.品牌='smart',IFNULL(b.`金融服务费`,0),0)) as  smart金融额外服务费,  " +
                "SUM(IF((e.品牌='MM' or e.品牌='MB' or e.品牌='AMG'),IFNULL(b.`金融服务费`,0),0)) as  MB金融额外服务费,  " +
                "SUM(IF(e.品牌='smart' AND  f.`状态`='11',b.`贷款额`,0)) smart贷款总金额,  " +
                "SUM(IF((e.品牌='MM' or e.品牌='MB' or e.品牌='AMG') AND  f.`状态`='11',b.`贷款额`,0)) MB贷款总金额  " +
                "from vw_retailed_quotation a      " +
                "join `报价金融表` b on a.金融 = b.`金融编号` and (b.`金融手续费` is not null)       " +
                "join LSH车型 e on a.车型编号 = e.车型编号   " +
                "LEFT JOIN `lsh金融申请` f ON f.`初始报价单号` = a.`初始报价单号`  " +
                "where a.车辆零售状态 = '已卖'   " +
                "and b.`贷款额`>0   " +
                "AND  DATE_FORMAT(a.开票时间,'%Y-%m') =:ym  " +
                "group by a.许可";*//*

        String startDate = this.getFirstDay(year, month), endDate = this.getLastDay(year, month);
        //MB先享后选台数
        selectSql = String.format("SELECT a.许可_BMBS 许可,  sum(IF(a.品牌='MM' or a.品牌='MB' or a.品牌='AMG',a.新车Agility台数,0)) MB先享后选台数 , " +
                "sum(IF(a.品牌='smart',a.新车Agility台数,0)) smart保值租购台数 " +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by   a.许可_BMBS    \n" , KPIUtils.agilityPenetrationRate);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB' = ''"," 1=1 ");
        values = this.getList(selectSql.toString());
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "MB先享后选台数");
            doUpdateFIReport(values, updateSql, year, "smart保值租购台数");
        }
        
        //smart金融台数，MB金融台数，smart金融手续费，MB金融手续费
        selectSql = String.format("SELECT a.许可_BMBS 许可,a.品牌 ,  sum(IF(a.品牌='smart',a.新车金融台数,0)) smart金融台数 , " +
                " sum(IF(a.品牌='MM' or a.品牌='MB' or a.品牌='AMG',a.新车金融台数,0)) MB金融台数, " +
                " ROUND(sum(IF(a.品牌='smart',a.金融手续费,0)),2) smart金融手续费, " +
                " ROUND(sum(IF(a.品牌='MM' or a.品牌='MB' or a.品牌='AMG',a.金融手续费,0)),2) MB金融手续费 " +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by   a.许可_BMBS    \n" , KPIUtils.financialInfo);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB' = ''"," 1=1 ");
        values = this.getList(selectSql.toString());
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "MB金融台数");
            doUpdateFIReport(values, updateSql, year, "smart金融台数");
            doUpdateFIReport(values, updateSql, year, "smart金融手续费");
            doUpdateFIReport(values, updateSql, year, "MB金融手续费");
        }
        
        //smart金融额外服务费(含税)、MB金融额外服务费（含税）
        selectSql = String.format("SELECT a.许可_BMBS 许可,a.品牌 ,  ROUND(sum(if(a.品牌='smart' ,a.金融服务费 ,0))*1.06,2) smart金融额外服务费 ," +
                " ROUND(sum(IF(a.品牌='MM' or a.品牌='MB' or a.品牌='AMG',a.金融服务费,0))*1.06,2) MB金融额外服务费" +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by   a.许可_BMBS    \n" , KPIUtils.financingServiceFeePerUnit);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB' = ''"," 1=1 ");
        values = this.getList(selectSql.toString());
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "smart金融额外服务费");
            doUpdateFIReport(values, updateSql, year, "MB金融额外服务费");
        }

        //smart贷款总金额、MB贷款总金额
        selectSql = String.format("SELECT a.许可_BMBS 许可,a.品牌 ,  ROUND(sum(if(a.品牌='smart' ,a.贷款总金额 ,0)),2) smart贷款总金额 ," +
                " ROUND(sum(IF(a.品牌='MM' or a.品牌='MB' or a.品牌='AMG',a.贷款总金额,0)),2) MB贷款总金额 " +
                " FROM  (%s) a\n" +
                " where a.`品牌` is  not  null group by   a.许可_BMBS    \n" , KPIUtils.loanTotal);
        selectSql = selectSql.replaceAll("2000-01-01", startDate);
        selectSql = selectSql.replaceAll("2020-01-01", endDate);
        selectSql = selectSql.replaceAll("'MB' = ''"," 1=1 ");
        values = this.getList(selectSql.toString());
        if (values != null) {
            doUpdateFIReport(values, updateSql, year, "smart贷款总金额");
            doUpdateFIReport(values, updateSql, year, "MB贷款总金额");
        }*/
    }

    /**
     * 根据许可和品牌初始化统计数据
     *
     * @param year
     * @throws Exception
     */
    private void initReport(String year) throws Exception {
        StringBuffer sb = new StringBuffer();

        sb.append(" INSERT INTO t_fi_performance_tracking_rpt(PERMIT, KPI_NAME, SYEAR)  ");
        sb.append(" SELECT a.许可_BMBS , b.KPINAME ,:syear FROM lsh经销商表 a,(SELECT KPINAME FROM t_report_kpi_name ) b ");
        sb.append(" WHERE NOT EXISTS (SELECT id FROM t_fi_performance_tracking_rpt c WHERE c.PERMIT= a.许可_BMBS AND c.KPI_NAME= b.KPINAME  AND c.SYEAR=:syear) AND a.是否测试='否' ");

        Query query = this.entities.createNativeQuery(sb.toString());
        query.setParameter("syear", year);
        query.executeUpdate();
    }

    private boolean isNull(Object value) {
        return value == null || value.toString().trim().equals("");
    }

    private String parseMonthToEn(String month) {
        String[] arr = {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"};
        Integer index = Integer.valueOf(month) - 1;
        return arr[index];
    }

    private void doUpdateFIReport(List<Map<String, Object>> values, String updateSql, String year, String kpiName) {
        for (Map<String, Object> value : values) {
            if (isNull(value.get("许可"))) continue;
            Query query = this.entities.createNativeQuery(updateSql.toString());
            query.setParameter("value", value.get(kpiName));
            query.setParameter("PERMIT", value.get("许可"));
            query.setParameter("KPI_NAME", kpiName);
            query.setParameter("SYEAR", year);
            query.executeUpdate();
        }
    }

    /**
     * @param year
     * @param month
     * @return
     */
    private String getFirstDay(String year, String month) {
        return year + "-" + month + "-01";
    }

    /**
     * @param year
     * @param month
     * @return
     */
    private String getLastDay(String year, String month) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Integer.parseInt(year), Integer.parseInt(month) - 1, 1);

        int maxDay = calendar.getActualMaximum(Calendar.DATE);

        return year + '-' + month + '-' + maxDay;
    }
}

